{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import statsmodels.api as sm\n",
    "import numpy as np\n",
    "\n",
    "# df = pd.read_excel(\"./案例.xlsx\")\n",
    "# df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Time</th>\n",
       "      <th>求和项:Volume</th>\n",
       "      <th>求和项:Amount</th>\n",
       "      <th>平均值项:AOIB</th>\n",
       "      <th>平均值项:VOIB</th>\n",
       "      <th>平均值项:M</th>\n",
       "      <th>R</th>\n",
       "      <th>ILC</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-08-31 21:00:00</td>\n",
       "      <td>27625</td>\n",
       "      <td>1909028650</td>\n",
       "      <td>410.456522</td>\n",
       "      <td>279403.945652</td>\n",
       "      <td>-2.691528e-06</td>\n",
       "      <td>-0.005056</td>\n",
       "      <td>0.015183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-08-31 21:01:00</td>\n",
       "      <td>6846</td>\n",
       "      <td>471696500</td>\n",
       "      <td>-401.874477</td>\n",
       "      <td>-281989.483264</td>\n",
       "      <td>-9.648648e-05</td>\n",
       "      <td>0.000726</td>\n",
       "      <td>1.808302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-08-31 21:02:00</td>\n",
       "      <td>6961</td>\n",
       "      <td>480025700</td>\n",
       "      <td>-436.841667</td>\n",
       "      <td>-306988.706250</td>\n",
       "      <td>-7.685044e-07</td>\n",
       "      <td>0.001451</td>\n",
       "      <td>0.095530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-08-31 21:03:00</td>\n",
       "      <td>8242</td>\n",
       "      <td>567319900</td>\n",
       "      <td>-44.900000</td>\n",
       "      <td>-36267.764583</td>\n",
       "      <td>-6.703983e-05</td>\n",
       "      <td>-0.002173</td>\n",
       "      <td>0.320540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-08-31 21:04:00</td>\n",
       "      <td>3927</td>\n",
       "      <td>270518400</td>\n",
       "      <td>247.870293</td>\n",
       "      <td>163940.866109</td>\n",
       "      <td>-5.701750e-06</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.117385</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12621</th>\n",
       "      <td>2022-10-31 14:55:00</td>\n",
       "      <td>2706</td>\n",
       "      <td>163370600</td>\n",
       "      <td>41.680851</td>\n",
       "      <td>15392.612766</td>\n",
       "      <td>-4.966436e-05</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.508388</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12622</th>\n",
       "      <td>2022-10-31 14:56:00</td>\n",
       "      <td>4133</td>\n",
       "      <td>249325850</td>\n",
       "      <td>966.016667</td>\n",
       "      <td>571869.264583</td>\n",
       "      <td>-1.413421e-07</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.015462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12623</th>\n",
       "      <td>2022-10-31 14:57:00</td>\n",
       "      <td>6158</td>\n",
       "      <td>372405950</td>\n",
       "      <td>-5.962343</td>\n",
       "      <td>-11447.368201</td>\n",
       "      <td>-1.262145e-05</td>\n",
       "      <td>0.002484</td>\n",
       "      <td>0.095576</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12624</th>\n",
       "      <td>2022-10-31 14:58:00</td>\n",
       "      <td>4757</td>\n",
       "      <td>288081550</td>\n",
       "      <td>544.158333</td>\n",
       "      <td>321925.820833</td>\n",
       "      <td>-1.165522e-06</td>\n",
       "      <td>0.000826</td>\n",
       "      <td>0.038372</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12625</th>\n",
       "      <td>2022-10-31 14:59:00</td>\n",
       "      <td>8125</td>\n",
       "      <td>492488600</td>\n",
       "      <td>1457.104167</td>\n",
       "      <td>874828.508333</td>\n",
       "      <td>-1.383448e-08</td>\n",
       "      <td>0.001651</td>\n",
       "      <td>-0.022745</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>12626 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     Time  求和项:Volume  求和项:Amount    平均值项:AOIB      平均值项:VOIB  \\\n",
       "0     2022-08-31 21:00:00       27625  1909028650   410.456522  279403.945652   \n",
       "1     2022-08-31 21:01:00        6846   471696500  -401.874477 -281989.483264   \n",
       "2     2022-08-31 21:02:00        6961   480025700  -436.841667 -306988.706250   \n",
       "3     2022-08-31 21:03:00        8242   567319900   -44.900000  -36267.764583   \n",
       "4     2022-08-31 21:04:00        3927   270518400   247.870293  163940.866109   \n",
       "...                   ...         ...         ...          ...            ...   \n",
       "12621 2022-10-31 14:55:00        2706   163370600    41.680851   15392.612766   \n",
       "12622 2022-10-31 14:56:00        4133   249325850   966.016667  571869.264583   \n",
       "12623 2022-10-31 14:57:00        6158   372405950    -5.962343  -11447.368201   \n",
       "12624 2022-10-31 14:58:00        4757   288081550   544.158333  321925.820833   \n",
       "12625 2022-10-31 14:59:00        8125   492488600  1457.104167  874828.508333   \n",
       "\n",
       "             平均值项:M         R       ILC  \n",
       "0     -2.691528e-06 -0.005056  0.015183  \n",
       "1     -9.648648e-05  0.000726  1.808302  \n",
       "2     -7.685044e-07  0.001451  0.095530  \n",
       "3     -6.703983e-05 -0.002173  0.320540  \n",
       "4     -5.701750e-06  0.000000  0.117385  \n",
       "...             ...       ...       ...  \n",
       "12621 -4.966436e-05  0.000000  0.508388  \n",
       "12622 -1.413421e-07  0.000000  0.015462  \n",
       "12623 -1.262145e-05  0.002484  0.095576  \n",
       "12624 -1.165522e-06  0.000826  0.038372  \n",
       "12625 -1.383448e-08  0.001651 -0.022745  \n",
       "\n",
       "[12626 rows x 8 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# df1 = pd.read_csv(\"./9.1.csv\")\n",
    "# df1\n",
    "df1 = pd.read_excel('./SHUJU改.xls')\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df.groupby(['城市']).get_group(\"北京\").iloc[[0, -1], [0]]\n",
    "def three_sigma(Ser1):\n",
    "    # '''\n",
    "    # Ser1：表示传入DataFrame的某一列。\n",
    "    # '''\n",
    "    rule = (Ser1.mean()-3*Ser1.std()>Ser1) | (Ser1.mean()+3*Ser1.std()< Ser1)\n",
    "    index = np.arange(Ser1.shape[0])[rule]\n",
    "    return index  #返回落在3sigma之外的行索引值\n",
    "\n",
    "def delete_out3sigma(data):\n",
    "\t# '''\n",
    "\t# data：待检测的DataFrame\n",
    "\t# '''\n",
    "    out_index = [] #保存要删除的行索引\n",
    "    for i in range(data.shape[1]): # 对每一列分别用3sigma原则处理\n",
    "        index = three_sigma(data.iloc[:,i])\n",
    "        out_index += index.tolist()\n",
    "    delete_ = list(set(out_index))\n",
    "    print('所删除的行索引为：',delete_)\n",
    "    data = data.drop(delete_)\n",
    "    return data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "所删除的行索引为： [0, 6144, 6149, 6154, 8202, 8203, 10250, 12305, 10258, 4118, 10266, 10267, 2086, 8231, 2088, 2089, 44, 12333, 12335, 53, 54, 12346, 10300, 6206, 6207, 12353, 6210, 68, 12358, 10313, 6217, 6218, 6219, 8265, 8274, 85, 10326, 10327, 10328, 10329, 94, 12383, 12384, 12389, 102, 104, 12392, 8299, 12396, 8301, 12399, 12400, 12401, 12402, 116, 10360, 6265, 10361, 10362, 10363, 4219, 121, 10367, 10368, 8321, 10369, 10370, 4226, 12414, 12422, 4233, 12427, 12428, 12430, 4239, 148, 12437, 12438, 4249, 10397, 2209, 161, 162, 12452, 12450, 2214, 8358, 4267, 12459, 8365, 2222, 8368, 4273, 8371, 8373, 2232, 2233, 4281, 8379, 12473, 12475, 6344, 6346, 205, 12494, 4308, 2274, 2275, 2281, 12523, 2290, 242, 4343, 12535, 12537, 6392, 2300, 258, 4356, 261, 8454, 8458, 10507, 8460, 6417, 8466, 6433, 4388, 4389, 10535, 12583, 12586, 298, 10540, 4397, 4398, 4399, 4400, 4401, 4402, 4403, 4404, 4405, 10543, 8504, 4411, 8508, 10557, 12607, 4415, 6466, 6467, 10563, 10564, 12614, 4423, 8522, 12618, 4431, 336, 337, 2383, 348, 349, 6492, 351, 2397, 2400, 2401, 2399, 4448, 4449, 10602, 2412, 2413, 2414, 2415, 2416, 4464, 4466, 4479, 2432, 6530, 8579, 2435, 2436, 10626, 10630, 4482, 8583, 2442, 3742, 396, 2446, 400, 2448, 6545, 403, 2454, 6553, 10655, 4512, 10657, 4516, 10661, 10662, 6567, 10663, 4517, 10666, 10667, 8615, 6574, 10670, 10671, 433, 4526, 6582, 2494, 6592, 464, 6620, 6621, 6622, 6623, 6624, 6626, 6627, 6628, 6629, 6637, 6638, 6639, 6640, 6641, 6642, 6643, 6644, 6645, 6646, 6650, 6654, 6663, 2571, 10766, 10769, 545, 551, 555, 6707, 2615, 2616, 4664, 10811, 571, 573, 2626, 589, 4685, 4689, 8785, 601, 6749, 6750, 6751, 6752, 8806, 2663, 2664, 8810, 4720, 4721, 4722, 4723, 629, 4726, 8825, 4730, 8826, 6780, 4729, 639, 2691, 4742, 10888, 10889, 10890, 10891, 10892, 8842, 8843, 10895, 4752, 657, 4753, 8848, 661, 671, 2721, 10913, 8872, 8874, 688, 689, 694, 696, 699, 2769, 2771, 4825, 8923, 8925, 4830, 8927, 2784, 736, 2786, 8928, 4846, 6895, 8950, 8951, 772, 773, 4873, 4875, 4880, 4881, 4886, 4887, 4888, 11033, 11034, 792, 795, 4890, 803, 11044, 8997, 9002, 6955, 9003, 817, 9020, 11069, 9026, 2883, 837, 840, 11082, 11084, 9039, 4945, 2898, 6995, 6996, 2899, 6998, 6999, 7000, 7001, 7002, 11100, 4965, 11129, 889, 894, 895, 899, 900, 11141, 5001, 11146, 9101, 5010, 5020, 9127, 11181, 942, 9135, 9136, 9138, 5046, 951, 9146, 9147, 7101, 7102, 3007, 7103, 7104, 7105, 7106, 7107, 7108, 7109, 3015, 7110, 7111, 11205, 7115, 7116, 9164, 9166, 9167, 9168, 7121, 9165, 11223, 11226, 989, 992, 5088, 12409, 3041, 11236, 7141, 7142, 11237, 1000, 11242, 7147, 5100, 5101, 5102, 11247, 7152, 9200, 5111, 3079, 7179, 11280, 11281, 7187, 1044, 7188, 7189, 1047, 7190, 7191, 7192, 7194, 9244, 11288, 11292, 9247, 11294, 11295, 7202, 7204, 9269, 3129, 3132, 11206, 11324, 3135, 11333, 9299, 3160, 5218, 9314, 9315, 9325, 11374, 11376, 5234, 11379, 1141, 5238, 3196, 3200, 9348, 9356, 5272, 9373, 11427, 11429, 11431, 11432, 11439, 11442, 9400, 7354, 9405, 11457, 3270, 5321, 5326, 7384, 1247, 11488, 3297, 11495, 5358, 3312, 3315, 3316, 7414, 11511, 1272, 7420, 1279, 11522, 9475, 9477, 9478, 3340, 7437, 9484, 9487, 5399, 7448, 9501, 3360, 5418, 5426, 11571, 5429, 3382, 11582, 11583, 11584, 5439, 11587, 1349, 11591, 5448, 5449, 5451, 11596, 7505, 7507, 1365, 3415, 7522, 11618, 7534, 1392, 7536, 5507, 5508, 5509, 7557, 11652, 7560, 7561, 7562, 7563, 9237, 5517, 7571, 1428, 11684, 9638, 5551, 7600, 7601, 7602, 7603, 7604, 7605, 7606, 7607, 1464, 11705, 1466, 7608, 7609, 7610, 7611, 7612, 7613, 5569, 7614, 3523, 7616, 1471, 7625, 7626, 9674, 9675, 9676, 7630, 7631, 7632, 7633, 7634, 7635, 7636, 3541, 3542, 7637, 7638, 7639, 7640, 7641, 7642, 7643, 3550, 7645, 7646, 3553, 3554, 3555, 9697, 3557, 9696, 7654, 1513, 1516, 9708, 9714, 3577, 11771, 9726, 5597, 1544, 3607, 7703, 1563, 5660, 7708, 9758, 7715, 7718, 7730, 5684, 5687, 7644, 5693, 3646, 3647, 5696, 5694, 3653, 3661, 7762, 5717, 5718, 7765, 11872, 11873, 11874, 11875, 11878, 11879, 11880, 11881, 11882, 11883, 11884, 3699, 1651, 3703, 3708, 9852, 1660, 9858, 9860, 9861, 7816, 11913, 7818, 5771, 1675, 7823, 5780, 11924, 11931, 11932, 11933, 9884, 11935, 9888, 11937, 9890, 3747, 5796, 9886, 1702, 3750, 9887, 1705, 11942, 1709, 1710, 3760, 3763, 9912, 5817, 7876, 1734, 1740, 1741, 1743, 7887, 7892, 3801, 12000, 7909, 7911, 1776, 12017, 3829, 1782, 7927, 3837, 3838, 3839, 7943, 1799, 12048, 12050, 12052, 12053, 10007, 10008, 5913, 5917, 12061, 1835, 10027, 3883, 10031, 10038, 12089, 7994, 1865, 8014, 1871, 8016, 1872, 3923, 1886, 12135, 12136, 12137, 8046, 10094, 8049, 12148, 10102, 6007, 6008, 12151, 12150, 12159, 1923, 12174, 12175, 12176, 1937, 1938, 1948, 12189, 10144, 8096, 1955, 8110, 1967, 1969, 1970, 1971, 8116, 8121, 8123, 10182, 4044, 8141, 12238, 1998, 8142, 10192, 10193, 2001, 10195, 4053, 4054, 4055, 4056, 4057, 4058, 2008, 4060, 4061, 8161, 6114, 6115, 12260, 10211, 6118, 6120, 6121, 6122, 6123, 6124, 6125, 4078, 6127, 12264, 6129, 12269, 10228, 12279]\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(                     Time  求和项:Volume  求和项:Amount    平均值项:AOIB      平均值项:VOIB  \\\n",
       " 1     2022-08-31 21:01:00        6846   471696500  -401.874477 -281989.483264   \n",
       " 2     2022-08-31 21:02:00        6961   480025700  -436.841667 -306988.706250   \n",
       " 3     2022-08-31 21:03:00        8242   567319900   -44.900000  -36267.764583   \n",
       " 4     2022-08-31 21:04:00        3927   270518400   247.870293  163940.866109   \n",
       " 5     2022-08-31 21:05:00        3334   229725950    97.224066   59552.336100   \n",
       " ...                   ...         ...         ...          ...            ...   \n",
       " 12621 2022-10-31 14:55:00        2706   163370600    41.680851   15392.612766   \n",
       " 12622 2022-10-31 14:56:00        4133   249325850   966.016667  571869.264583   \n",
       " 12623 2022-10-31 14:57:00        6158   372405950    -5.962343  -11447.368201   \n",
       " 12624 2022-10-31 14:58:00        4757   288081550   544.158333  321925.820833   \n",
       " 12625 2022-10-31 14:59:00        8125   492488600  1457.104167  874828.508333   \n",
       " \n",
       "              平均值项:M         R       ILC  \n",
       " 1     -9.648648e-05  0.000726  1.808302  \n",
       " 2     -7.685044e-07  0.001451  0.095530  \n",
       " 3     -6.703983e-05 -0.002173  0.320540  \n",
       " 4     -5.701750e-06  0.000000  0.117385  \n",
       " 5     -2.705257e-05  0.000726  0.203557  \n",
       " ...             ...       ...       ...  \n",
       " 12621 -4.966436e-05  0.000000  0.508388  \n",
       " 12622 -1.413421e-07  0.000000  0.015462  \n",
       " 12623 -1.262145e-05  0.002484  0.095576  \n",
       " 12624 -1.165522e-06  0.000826  0.038372  \n",
       " 12625 -1.383448e-08  0.001651 -0.022745  \n",
       " \n",
       " [11822 rows x 8 columns],\n",
       " 12626,\n",
       " 11822)"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "df2 = delete_out3sigma(df1)\n",
    "df2, len(df1), len(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df2 = df1.groupby(['Time']).apply(lambda i: i.iloc[[0, -1], [1]])\n",
    "# df2\n",
    "# df3 = df2.drop([1, 3])\n",
    "# df3\n",
    "df2.to_csv('4.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2.reset_index('Time')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2.to_csv('1.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df4 = pd.DataFrame(df1.groupby(['Time']).apply(lambda subf: (subf['R'].tolist()[-1] - subf['R'].tolist()[0])/subf['R'].tolist()[0]))\n",
    "df4.to_csv('2.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1.groupby(['Time']).apply(lambda i: i.iloc[[-1], [1]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1.groupby(['Time']).apply(lambda i: i.iloc[[0, -1], [0,1,2,3,]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df6 = df1.groupby(['Time']).apply(lambda subf: sm.OLS(subf['ILT'].tolist(), sm.add_constant(subf['VOIB'].tolist())).fit().params[0])\n",
    "df6"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.DataFrame(df6).to_csv('3.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "model= sm.OLS([3,5,7,9,11], sm.add_constant([1,2,3,4,5])).fit()\n",
    "print(model.summary())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "model.params[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.9"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
